Selecting Scalar Values

A scalar value refers to a single value. For example, string number. So the underlying query will return a single value that will need to be converted. CA.Blocks provide two methods for selecting scalar values along with their asynchronous counterparts.

ExecuteScalarAs<T>

The vast majority of the time you are going to know the return type, in this case, you can use the ExecuteScalarAs this is the fastest method to call however the object is as cast as an expected type as such you need to match the return type with the type given from the data source

In the example below we will return an integer value as a count of the [Production].[Product] so once we have created the command we call ExecuteScalarAs(cmd) this will get the values as an integer and cast the result value as an integer.

    public int GetProductionProductCount()
    {
        var cmd = CreateTextCommand("select count(*) from [Production].[Product]");
        return ExecuteScalarAs<int>(cmd);
    }

ExecuteScalarAsAsync<T>

Async version of ExecuteScalarAs

    public Task<int> GetProductionProductCountAsync()
    {
        var cmd = CreateTextCommand("select count(*) from [Production].[Product]");
        return ExecuteScalarAsAsync<int>(cmd);
    }

ExecuteScalarWithConvertAs<T>

There are times when the result type from the source system is not the desired type. In the example below the type coming back from the source system is a byte. We may what to return the type as a string. In this case, we can use the ExecuteScalarWithConvertAs function. This will get the value from the system as a byte but will convert the value to a string.

        public string GetValueThatMustBeConvertedToString()
        {
            // Here we getting a values as a byte from the server but returning the values as a string
            var cmd = CreateTextCommand("Select Cast(123 as tinyint) as ExampleOfConvert");
            return ExecuteScalarWithConvertAs<string>(cmd);
        }

As an example of this in reverse where the source type in a string but you need the data as a byte.

        public byte GetValueThatMustBeConvertedToByte()
        {
            // Here we getting a values as a string from the server but returning the values as a byte
            var cmd = CreateTextCommand("Select '123' as ExampleOfConvert");
            return ExecuteScalarWithConvertAs<byte>(cmd);
        }

• Note you will get a convert exception if the conversion is not possible

     public byte GetValueThatMustBeConvertedToByt_Exception()
        {
            // Here we getting a values as a byte from the server but returning the values as a string the string value cannot be converted to a byte.
            var cmd = CreateTextCommand("Select '1234' as ExampleOfConvert");
            // this will 
            return ExecuteScalarWithConvertAs<byte>(cmd);
            
        }

ExecuteScalar

The ExecuteScalar will return the value directly as an object this case you can deal with the conversion as needed. This method is simply managing the connection leaving the code to deal with the conversion.

    public object GetSysObjectsCount()
    {
        var cmd = CreateTextCommand("select count(*) from [Production].[Product]");
        return ExecuteScalar(cmd);
    }